--DROP TABLE PeopleMain;
CREATE TABLE PeopleMain
(
	PKey		INT PRIMARY KEY IDENTITY(1001,1),
	[Name]		nVARCHAR(200),
	DoB			DATETIME,
	IMDbCode	VARCHAR(10)		DEFAULT 'nm0000000' CHECK (IMDbCode LIKE 'nm%'), --check format in app
	IMDbLink	VARCHAR(100)	DEFAULT 'http://www.imdb.com/find?s=all&q=',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d')),
	CONSTRAINT UK_PeopleMain_NameCode UNIQUE(Name ASC,IMDbCode ASC)
);
INSERT INTO PeopleMain(Name,DoB,IMDbLink)
SELECT DISTINCT tmpDirector,'1800-01-01','http://www.imdb.com/find?s=all&q='+REPLACE(REPLACE(REPLACE(REPLACE(tmpDirector,' ','+'),'.','+'),'&',''),'++','+') FROM EntertainmentMain WHERE tmpDirector<>''
SELECT * FROM PeopleMain

/* --REMOVE during Data Transfer accross DBs
IF EXISTS (SELECT * FROM PeopleMain WHERE IMDbLink LIKE '%find?s=all&q=%')
	UPDATE PeopleMain SET IMDbLink=IMDbLink+REPLACE(Name,' ','+') WHERE IMDbLink LIKE '%find?s=all&q=%'
ELSE UPDATE PeopleMain SET IMDbLink='http://www.imdb.com/name/'+IMDbCode+'/';
*/

--DROP TABLE EntertainmentMain;
CREATE TABLE EntertainmentMain
(
	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
	Stage_PKey		INT,
	SerialNo		INT		NOT NULL	UNIQUE,
	Title			nVARCHAR(500)	NOT NULL,
	AlternateTitle	nVARCHAR(500)	NOT NULL,
	tmpDirector		nVARCHAR(500),
	tmpGenre		nVARCHAR(200),
	tmpCountry		nVARCHAR(200),
	Poster			nVARCHAR(100),
	PosterLocation	nVARCHAR(255),
	FileLocation	nVARCHAR(255),
	VolumeMainPKey	INT,	--REFERENCES
	Classification	nVARCHAR(200),
	nDiscs			INT,
	nSeason			INT,	--BLANK for Muvee records
	nEpisode		INT,	--BLANK for Muvee records
	RunTimeMins		INT,
	ReleaseYear		INT		DEFAULT 0	CHECK(ReleaseYear>=1800 AND ReleaseYear<=2100),
	Studio			nVARCHAR(200),
	SeriesParentPKey	INT,	--BLANK for Muvee records, populated only for episodes
	CollectionMainPKey	INT,	--REFERENCES
	LayerMainPKey	INT	REFERENCES LayerMain(PKey),
	ColourMainPKey	INT	REFERENCES ColourMain(PKey),
	RegionMainPKey	INT	REFERENCES RegionMain(PKey),
	MediumMainPKey	INT	REFERENCES MediumMain(PKey),
	VCodecMainPKey	INT	REFERENCES VCodecMain(PKey),
	ACodecMainPKey	INT,	--REFERENCES
	tmpSeen			BIT,
	tmpLoaned		BIT,
	CondMainPKey	INT		REFERENCES CondMain(PKey),
	Casting			nVARCHAR(Max),
	Plot			nVARCHAR(Max),
	Notes			nVARCHAR(Max),
	IMDbRating		FLOAT	CHECK(IMDbRating>=0 AND IMDbRating<=10),
	CustomRating	FLOAT	CHECK(CustomRating>=0 AND CustomRating<=10),
	IMDbCode		VARCHAR(20)		DEFAULT 'tt0000000' CHECK (IMDbCode LIKE 'tt%'), --check format in app
	IMDbLink		VARCHAR(100)	DEFAULT 'http://www.imdb.com/',
	IMDbTrailerLink	VARCHAR(100)	DEFAULT 'http://www.imdb.com/',
	OfficialSite	VARCHAR(500)	DEFAULT '',
	WikipediaLink	VARCHAR(500)	DEFAULT 'http://en.wikipedia.org' COLLATE SQL_Latin1_General_CP1_CS_AS,
	MyReviews		nVARCHAR(Max)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
UPDATE EntertainmentMain 	SET tmpDirector=(SELECT Director FROM Stage WHERE StagePKey=movie_id)
UPDATE EntertainmentMain	SET	IMDbLink='http://www.imdb.com/find?s=all&q='+REPLACE(Title,' ','+')	WHERE IMDbCode='tt0000000';
UPDATE EntertainmentMain	SET	IMDbTrailerLink='http://www.imdb.com/find?s=all&q='+REPLACE(Title,' ','+')	WHERE IMDbCode='tt0000000';
UPDATE EntertainmentMain	SET IMDbLink='http://www.imdb.com/title/'+IMDbCode+'/'	WHERE IMDbCode<>'tt0000000';
UPDATE EntertainmentMain	SET IMDbTrailerLink='http://www.imdb.com/title/'+IMDbCode+'/trailers'	WHERE IMDbCode<>'tt0000000';
*/

/* -- NOT REQD.: Merged as EntertainmentMain
--DROP TABLE SeriesMain;
CREATE TABLE SeriesMain
(
	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
	Stage_PKey		INT	DEFAULT NULL,
	SerialNo		INT		NOT NULL	UNIQUE,
	Title			nVARCHAR(500)	NOT NULL,
	AlternateTitle	nVARCHAR(500)	NOT NULL,
	tmpDirector		nVARCHAR(500),
	tmpGenre		nVARCHAR(200),
	tmpCountry		nVARCHAR(200),
	Poster			nVARCHAR(100),
	VolumeMainPKey	INT,	--REFERENCES
	Classification	nVARCHAR(200),
	nDiscs			INT,	--ONLY for TV Series record
	nSeason			INT,	--ZERO for Episode record
	nEpisode		INT,	--ZERO for Episode record
	RunTimeMins		INT,	--ZERO for Episode record
	ReleaseYear		INT		DEFAULT 0	CHECK(ReleaseYear>=1800 AND ReleaseYear<=2100),
	Studio			nVARCHAR(200),
	SeriesParentPKey	INT,	--BLANK if TV Series record, populated only for episodes
	CollectionMainPKey	INT,	--REFERENCES
	LayerMainPKey	INT	REFERENCES LayerMain(PKey),
	ColourMainPKey	INT	REFERENCES ColourMain(PKey),
	RegionMainPKey	INT	REFERENCES RegionMain(PKey),
	MediumMainPKey	INT	REFERENCES MediumMain(PKey),
	VCodecMainPKey	INT	REFERENCES VCodecMain(PKey),
	ACodecMainPKey	INT,	--REFERENCES
	tmpSeen			BIT,
	tmpLoaned		BIT,
	CondMainPKey	INT		REFERENCES CondMain(PKey),
	Casting			nVARCHAR(Max),
	Plot			nVARCHAR(Max),
	Notes			nVARCHAR(Max),
	IMDbRating		FLOAT	CHECK(IMDbRating>=0 AND IMDbRating<=10),
	CustomRating	FLOAT	CHECK(CustomRating>=0 AND CustomRating<=10),
	IMDbCode		VARCHAR(20)		DEFAULT 'tt0000000' CHECK (IMDbCode LIKE 'tt%'), --check format in app
	IMDbLink		VARCHAR(100)	DEFAULT 'http://www.imdb.com/',
	IMDbTrailerLink	VARCHAR(100)	DEFAULT 'http://www.imdb.com/',
	OfficialSite	VARCHAR(500)	DEFAULT '',
	WikipediaLink	VARCHAR(500)	DEFAULT 'http://en.wikipedia.org' COLLATE SQL_Latin1_General_CP1_CS_AS,
	MyReviews		nVARCHAR(Max)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
--UPDATE muveemain 	SET tmpDirector=(SELECT Director FROM Stage WHERE StagePKey=movie_id)
--UPDATE EntertainmentMain	SET	IMDbLink='http://www.imdb.com/find?s=all&q='+REPLACE(Title,' ','+')	WHERE IMDbCode='tt0000000';
--UPDATE EntertainmentMain	SET	IMDbTrailerLink='http://www.imdb.com/find?s=all&q='+REPLACE(Title,' ','+')	WHERE IMDbCode='tt0000000';
--UPDATE EntertainmentMain	SET IMDbLink='http://www.imdb.com/title/'+IMDbCode+'/'	WHERE IMDbCode<>'tt0000000';
--UPDATE EntertainmentMain	SET IMDbTrailerLink='http://www.imdb.com/title/'+IMDbCode+'/trailers'	WHERE IMDbCode<>'tt0000000';
*/


--=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-
--DROP TABLE
--CREATE TABLE ..
--(
--	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
--	
--	DateModified	DATETIME	DEFAULT GetDate(),
--	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
--)
--=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-=-=-=-=-=--=-

--DROP TABLE UserMain
CREATE TABLE UserMain
(
	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
	UserName		nVARCHAR(25)	NOT NULL	UNIQUE,
	UserPassword	nVARCHAR(100)	NOT NULL,
	SecQues			nVARCHAR(100)	NOT NULL,
	SecAns			nVARCHAR(100)	NOT NULL,
	LoginCount		BIGINT		DEFAULT 0,
	NeedsPswdChng	INT			DEFAULT 0	CHECK (NeedsPswdChng IN (0,1,2,3)),
	DateRegistered	DATETIME	DEFAULT	GETDATE(),
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
INSERT INTO UserMain(UserName, UserPassword, SecQues, SecAns) VALUES('admin','90rzgNplKpVbKXPWtuNuQ+cAR9oB1Tv6B1c9cC7/l8w=','NA','NA') --Default Pswd: admin007

--DROP TABLE ApprovalMain
CREATE TABLE ApprovalMain
(
	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
	UserMainPKey	INT REFERENCES UserMain(PKey) ON DELETE CASCADE,
	ApprovalState	nVARCHAR(20)	NOT NULL	DEFAULT 'Pending'	CHECK (ApprovalState IN ('Admin','Approved','Pending','Revoked','Rejected')),
	Comments		nVARCHAR(500),
	ApprovalDate	DATETIME	DEFAULT	GETDATE(),
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
INSERT INTO ApprovalMain(UserMainPKey, ApprovalState) SELECT PKey, 'Admin' FROM UserMain WHERE UserName = 'admin'

--DROP TABLE
CREATE TABLE AwardMain
(
	PKey			INT	PRIMARY KEY IDENTITY(1001,1),
EntertainmentMainPKey	INT,
AwardType,
AwardCategory,
AwardNominee,
AwardYear	INT,
AwardWon	BIT,

	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE CastingMain
CREATE TABLE CastingMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	EntertainmentMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey) ON DELETE CASCADE,
	Casting			nVARCHAR(MAX) NOT NULL,
--	CharacterName	nVARCHAR(200) NOT NULL,
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE MuveeCastingRel;
CREATE TABLE MuveeCastingRel
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
--	PeopleMainPKey	INT NOT NULL REFERENCES PeopleMain(PKey),
	CastingMainPKey	nVARCHAR(Max),
	CharacterName	nVARCHAR(200) NOT NULL,
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE MuveeDirectorRel;
CREATE TABLE MuveeDirectorRel
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
	PeopleMainPKey	INT NOT NULL REFERENCES PeopleMain(PKey),
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
INSERT INTO MuveeDirectorRel(MuveeMainPKey,PeopleMainPKey)
SELECT DISTINCT mv.PKey AS MuveeMainPKey,ppl.PKey AS PeopleMainPKey FROM EntertainmentMain mv, PeopleMain ppl WHERE mv.tmpDirector=ppl.Name AND ppl.Status<>'d'
ORDER BY 1 ASC

--DROP TABLE LanguageMain;
CREATE TABLE LanguageMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	Code			VARCHAR(2)		NOT NULL	UNIQUE,
	Language		VARCHAR(20)		NOT NULL	UNIQUE,
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO LanguageMain(Code, Language) VALUES ('en','English');
INSERT INTO LanguageMain(Code, Language) VALUES ('es','Spanish');
INSERT INTO LanguageMain(Code, Language) VALUES ('hi','Hindi');
INSERT INTO LanguageMain(Code, Language) VALUES ('bn','Bengali');
*/

--DROP TABLE CountryMain;
CREATE TABLE CountryMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	Code			VARCHAR(3)		NOT NULL	UNIQUE,
	Country			VARCHAR(100)		NOT NULL	UNIQUE,
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO CountryMain VALUES ('Arg','Argentina',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Aus','Australia',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ast','Austria',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Bel','Belgium',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Bra','Brazil',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Can','Canada',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Chl','Chile',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Chi','China',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Cub','Cuba',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Czr','Czech Republic',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Den','Denmark',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Fin','Finland',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Fra','France',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ger','Germany',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Hon','Hong Kong',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Hun','Hungary',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ire','Ireland',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ind','India',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Irn','Iran',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ire','Ireland',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Ita','Italy',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Jap','Japan',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Lit','Lithuania',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Lux','Luxembourg',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Mal','Malta',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Mex','Mexico',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Mon','Monaco',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Net','Netherlands',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Nwz','New Zealand',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Nor','Norway',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Pak','Pakistan',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Per','Peru',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Pol','Poland',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Rom','Romania',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Rus','Russia',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Saf','South Africa',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Sko','South Korea',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Esp','Spain',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Swi','Switzerland',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Tha','Thailand',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Uk','UK',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Uru','Uruguay',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Usa','USA',current_timestamp,'n');
INSERT INTO CountryMain VALUES ('Wge','West Germany',current_timestamp,'n');
*/

--DROP TABLE MuveeCountryRel;
CREATE TABLE MuveeCountryRel
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
	CountryMainPKey	INT NOT NULL REFERENCES CountryMain(PKey),
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE GenreMain
CREATE TABLE GenreMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Genre			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO GenreMain(Genre) VALUES ('Action');
INSERT INTO GenreMain(Genre) VALUES ('Adventure');
INSERT INTO GenreMain(Genre) VALUES ('Animation');
INSERT INTO GenreMain(Genre) VALUES ('Biography');
INSERT INTO GenreMain(Genre) VALUES ('Comedy');
INSERT INTO GenreMain(Genre) VALUES ('Crime');
INSERT INTO GenreMain(Genre) VALUES ('Documentary');
INSERT INTO GenreMain(Genre) VALUES ('Drama');
INSERT INTO GenreMain(Genre) VALUES ('Family');
INSERT INTO GenreMain(Genre) VALUES ('Fantasy');
INSERT INTO GenreMain(Genre) VALUES ('Film-Noir');
INSERT INTO GenreMain(Genre) VALUES ('History');
INSERT INTO GenreMain(Genre) VALUES ('Horror');
INSERT INTO GenreMain(Genre) VALUES ('Music');
INSERT INTO GenreMain(Genre) VALUES ('Musical');
INSERT INTO GenreMain(Genre) VALUES ('Mystery');
INSERT INTO GenreMain(Genre) VALUES ('Romance');
INSERT INTO GenreMain(Genre) VALUES ('Sci-Fi');
INSERT INTO GenreMain(Genre) VALUES ('Short');
INSERT INTO GenreMain(Genre) VALUES ('Sport');
INSERT INTO GenreMain(Genre) VALUES ('Thriller');
INSERT INTO GenreMain(Genre) VALUES ('War');
INSERT INTO GenreMain(Genre) VALUES ('Western');
*/

--DROP TABLE MuveeGenreRel;
CREATE TABLE MuveeGenreRel
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
	GenreMainPKey	INT NOT NULL REFERENCES GenreMain(PKey),
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE MuveeWatchedRel;
CREATE TABLE MuveeWatchedRel
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
	DateWatched		DATETIME,
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE LayerMain
CREATE TABLE LayerMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Layer			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO LayerMain(Layer) VALUES('N/A');
INSERT INTO LayerMain(Layer,Description) VALUES('SSSL','Single Side, Single Layer');
INSERT INTO LayerMain(Layer,Description) VALUES('SSDL','Single Side, Dual Layer');
INSERT INTO LayerMain(Layer,Description) VALUES('DSSL','Dual Side, Single Layer');
INSERT INTO LayerMain(Layer,Description) VALUES('DSDL','Dual Side, Dual Layer');
*/

--DROP TABLE ColourMain
CREATE TABLE ColourMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Colour			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO ColourMain(Colour) VALUES ('N/A');
INSERT INTO ColourMain(Colour) VALUES ('Colour');
INSERT INTO ColourMain(Colour) VALUES ('Black and White');
INSERT INTO ColourMain(Colour) VALUES ('Mixed');
*/

--DROP TABLE RegionMain
CREATE TABLE RegionMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	Region			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO RegionMain VALUES ('Region 0','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 1','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 2','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 3','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 4','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 5','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 6','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 7','',current_timestamp,'n');
INSERT INTO RegionMain VALUES ('Region 8','',current_timestamp,'n');
*/

--DROP TABLE VolumeMain
CREATE TABLE VolumeMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Volume			VARCHAR(50)		NOT NULL	UNIQUE,
	DateProcessed	DATETIME,		
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
update EntertainmentMain set VolumeMainPKey=0 where VolumeMainPKey=5;
declare @pkey int; set @pkey=1001;
if not exists (select 1 from VolumeMain where pkey=1000) insert into VolumeMain(volume) values ('00 TO BURN');
while (select max(pkey) from VolumeMain) <1199
begin
insert into VolumeMain(volume) values ('DemoValue: '+convert(varchar,@pkey))
set @pkey=@pkey+1
end;
update EntertainmentMain set VolumeMainPKey=VolumeMainPKey+1000;
*/

--DROP TABLE CondMain
CREATE TABLE CondMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	Condition		VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE MediumMain
CREATE TABLE MediumMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Medium			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO MediumMain(Medium) VALUES('N/A');
INSERT INTO MediumMain(Medium) VALUES ('DVD');
INSERT INTO MediumMain(Medium) VALUES ('DVD-R');
INSERT INTO MediumMain(Medium) VALUES ('DVD-RW');
INSERT INTO MediumMain(Medium) VALUES ('DVD+R');
INSERT INTO MediumMain(Medium) VALUES ('DVD+RW');
INSERT INTO MediumMain(Medium) VALUES ('DVD-RAM');
INSERT INTO MediumMain(Medium) VALUES ('CD');
INSERT INTO MediumMain(Medium) VALUES ('CD-RW');
INSERT INTO MediumMain(Medium) VALUES ('VCD');
INSERT INTO MediumMain(Medium) VALUES ('SVCD');
INSERT INTO MediumMain(Medium) VALUES ('VHS');
INSERT INTO MediumMain(Medium) VALUES ('BETACAM');
INSERT INTO MediumMain(Medium) VALUES ('LaserDisc');
*/

--DROP TABLE VCodecMain
CREATE TABLE VCodecMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	VCodec			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO VCodecMain(VCodec) VALUES('N/A');
INSERT INTO VCodecMain(VCodec) VALUES('MPEG-1');
INSERT INTO VCodecMain(VCodec) VALUES('MPEG-2');
INSERT INTO VCodecMain(VCodec) VALUES('XviD');
INSERT INTO VCodecMain(VCodec) VALUES('DivX');
INSERT INTO VCodecMain(VCodec) VALUES('H.264');
INSERT INTO VCodecMain(VCodec) VALUES('RealVideo');
INSERT INTO VCodecMain(VCodec) VALUES('QuickTime (*.mov)');
INSERT INTO VCodecMain(VCodec) VALUES('Windows Media Video (*.wmv)');
INSERT INTO VCodecMain(VCodec) VALUES('MPEG-4 Visual');
INSERT INTO VCodecMain(VCodec) VALUES('NTSC/PAL');
INSERT INTO VCodecMain(VCodec) VALUES('VCD format (*.dat)');
INSERT INTO VCodecMain(VCodec) VALUES('Video Object (*.vob)');
INSERT INTO VCodecMain(VCodec) VALUES('Matroska Video (*.mkv)');
*/

--DROP TABLE ACodecMain
CREATE TABLE ACodecMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	ACodec			VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
/* --REMOVE during Data Transfer accross DBs
INSERT INTO ACodecMain(ACodec) VALUES('AC-3 Dolby audio');
INSERT INTO ACodecMain(ACodec) VALUES('OGG');
INSERT INTO ACodecMain(ACodec) VALUES('Lame MP3');
INSERT INTO ACodecMain(ACodec) VALUES('MPEG-1');
INSERT INTO ACodecMain(ACodec) VALUES('MPEG-2');
INSERT INTO ACodecMain(ACodec) VALUES('AAC');
INSERT INTO ACodecMain(ACodec) VALUES('Windows Media Audio');
*/

--DROP TABLE CollectionMain
CREATE TABLE CollectionMain
(
	PKey			INT PRIMARY KEY IDENTITY(1000,1),
	Collection		VARCHAR(50)		NOT NULL	UNIQUE,
	Description		nVARCHAR(200)	DEFAULT '',
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE UserMain
CREATE TABLE UserMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	UserId			VARCHAR(15)	CHECK(IsNumeric(RIGHT(UserId,8))=1), --INITIALS + CURRENT_TIMESTAMP(yyyymmdd)
	Initials		VARCHAR(5),
	FirstName		VARCHAR(30)	NOT NULL,
	MidName			VARCHAR(30),
	LastName		VARCHAR(30)	NOT NULL,
	DateCreated		DATETIME	DEFAULT GetDate(),
	Mobile			VARCHAR(15),
	Email			VARCHAR(100) DEFAULT 'id@domain.com', -- CHECK (), --validate email format
	CorpEmail		VARCHAR(100) DEFAULT 'id@domain.com', -- CHECK (), --validate email format
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE LoanMain;
CREATE TABLE LoanMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	MuveeMainPKey	INT NOT NULL REFERENCES EntertainmentMain(PKey),
	UserMainPKey	INT NOT NULL REFERENCES UserMain(PKey),
	DateFrom		DATETIME,	--DateLoaned
	DateThru	DATETIME,	--DateReturned
	DateModified	DATETIME	DEFAULT GetDate(),
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE ErrorLogMain
CREATE TABLE ErrorLogMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	ErrInitiator	nVARCHAR(500)	NOT NULL,
	ErrSource		nVARCHAR(500)	NOT NULL, --Method Name
	ErrMessage		nVARCHAR(500)	NOT NULL,
	ErrTargetSite	nVARCHAR(1000)	NOT NULL,
	ErrDescription	nVARCHAR(5000) DEFAULT '',
	ErrStackTrace	nVARCHAR(Max) DEFAULT '',
	ErrComments		nVARCHAR(1000)	DEFAULT '',
	ErrDate			DATETIME	DEFAULT current_timestamp,
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);

--DROP TABLE SettingsMain
CREATE TABLE SettingsMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	Meta			nVARCHAR(50),
	ID				nVARCHAR(50),
	[Value]			nVARCHAR(100),
	DefaultValue	nVARCHAR(100),
	Sort			INT,
	DateModified	DATETIME	DEFAULT current_timestamp,
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
	,CONSTRAINT UK_SettingsMain_MetaID UNIQUE (Meta ASC,ID ASC)
	,CONSTRAINT UK_SettingsMain_MetaSort UNIQUE (Meta ASC,Sort ASC)
);

--DROP TABLE WatchlistMain
CREATE TABLE WatchlistMain
(
	PKey			INT PRIMARY KEY IDENTITY(1001,1),
	EntertainmentMainPKey	INT	REFERENCES EntertainmentMain(PKey),
	DateWatched		DATETIME,
	DateAdded		DATETIME	DEFAULT current_timestamp,
	DateModified	DATETIME	DEFAULT current_timestamp,
	Status			CHAR(1)		DEFAULT 'n'	CHECK (Status IN ('n','u','d'))
);
--INSERT INTO WatchlistMain SELECT PKey,'1900-01-01','1900-01-01',current_timestamp,'n' FROM EntertainmentMain WHERE tmpSeen='1'

--DROP TABLE WatchlistIMDb
CREATE TABLE WatchlistIMDb
(
	BatchID			nVARCHAR(20), -- YYYYMMDDHHMMSS
	Position		INT,
	IMDbCode		VARCHAR(20),
	DateCreated		DATETIME,
	DateModified	DATETIME,
	Description		nVARCHAR(500),
	IMDbCode		VARCHAR(20),
	Title			nVARCHAR(500),
	TitleType		nVARCHAR(500),
	Directors		nVARCHAR(500),
	MyRating		INT,
	IMDbRating		FLOAT,
	RuntimeMins		INT,
	Year			INT,
	Genre			nVARCHAR(200),
	nVotes			INT,
	ReleaseDate		DATETIME,
	URL				VARCHAR(100)
);

/*
--EXAMPLE:
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS 
BEGIN
   DECLARE @retval int
   SELECT @retval = COUNT(*) FROM CheckTbl
   RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO


*/

--DROP TABLE Stage;
CREATE TABLE Stage
(
	movie_id		INT,
	number			INT,
	collection_id	INT,
	volume_id		INT,
	medium_id		INT,
	vcodec_id		INT,
	loaned			BIT,
	seen			BIT,
	rating			INT,
	color			INT,
	cond			INT,
	layers			INT,
	region			INT,
	media_num		INT,
	runtime			INT,
	year			INT,
	o_title			nVARCHAR(200), --VARCHAR(255)
	title			nVARCHAR(200), --VARCHAR(255)
	director		nVARCHAR(200), --VARCHAR(255)
	o_site			nVARCHAR(100), --VARCHAR(255)
	site			nVARCHAR(100), --VARCHAR(255)
	trailer			nVARCHAR(100), --VARCHAR(256)
	country			nVARCHAR(100), --VARCHAR(128)
	genre			nVARCHAR(100), --VARCHAR(128)
	image			nVARCHAR(200), --VARCHAR(128)
	studio			nVARCHAR(200), --VARCHAR(128)
	classification	nVARCHAR(max), --VARCHAR(128)
	cast			nVARCHAR(Max),
	plot			nVARCHAR(Max),
	notes			nVARCHAR(Max)
);

/* --REMOVE during Data Transfer accross DBs
INSERT INTO EntertainmentMain
(
	Stage_PKey,
	SerialNo,
	CollectionMainPKey,
	VolumeMainPKey,
	MediumMainPKey,
	VCodecMainPKey,
	tmpLoaned,
	tmpSeen,
	IMDbRating,
	ColourMainPKey,
	CondMainPKey,
	LayerMainPKey,
	RegionMainPKey,
	tmpMediaNum,
	RunTimeMins,
	ReleaseYear,
	Title,
	AlternateTitle,
	tmpDirector,
	OfficialSite,
	IMDbLink,
	IMDbTrailerLink,
	--Country,
	tmpGenre,
	Poster,
	Studio,
	Classification,
	Casting,
	Plot,
	Notes
) SELECT	
	movie_id,
	number,
	collection_id,
	volume_id,
	medium_id+1000,
	vcodec_id+1000,
	loaned,
	seen,
	rating,
	color+1001,
	cond,
	layers+1000,
	region+1001,
	media_num,
	runtime,
	year,
	o_title,
	title,
	--director,
	o_site,
	site,
	trailer,
	--country,
	genre,
	image,
	studio,
	classification,
	cast,
	plot,
	notes
FROM Stage	;

*/

/* Regular Expression
__email
\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*

__url
http(s)?://([\w-]+\.)+[\w-]+(/[\w- ./?%&=]*)?
*/

/*
::media
LaserDisc
BETACAM
VHS
SVCD
VCD
CD-RW
CD
DVD-RAM
DVD+RW
DVD+R
DVD-RW
DVD-R
DVD

::vid codecs
Matroska Video (*.mkv)
Video Object (*.vob)
VCD format (*.dat)
NTSC/PAL
MPEG-4 Visual
Windows Media Video (*.wmv)
QuickTime (*.mov)
RealVideo
H.264
DivX
XviD
MPEG-2
MPEG-1

::audio codecs
Lame MP3
Windows Media Audio
AAC
MPEG-2
MPEG-1
MP3
OGG
AC-3 Dolby audio


::audio channels
stereo
mono
5.1
7.1


::subtitle
embedded subtitle
Advanced Sub Station Alpha (.ssa)
Sub Station Alpha (.ssa)
SubViewer2 (.sub)
SubRip (.srt)
MicroDVD (.sub)
MPL2 (.txt)
DVD VOB


::lang
Turkish
Swedish
Spanish
Slovakian
Simplified Chinese
Russian
Portuguese
Polish
Pashto
Occitan
Norwegian Bokmal
Korean
Japanese
Italian
Indonesian
Hungarian
Greek
German
French
Estonian
English
Dutch
Danish
Czech
Catalan
Bulgarian
Brazilian Portuguese

*/

/*
alter table muveemain
add constraint FK__MuveeMain__CondM__20111126
foreign key (CondMainPKey)
REFERENCES CondMain(PKey)

*/